package vn.vnu.uet.university.bean;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DatabaseHandler extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "quanLySinhVien";

	// Contacts table name
	private static final String TABLE_USER = "user";
	private static final String TABLE_NEWS = "tinTuc";
	private static final String TABLE_REPORT = "thongBao";
	private static final String TABLE_SCORE = "diem";
	private static final String TABLE_SCHEDULE = "tkb";
	
	private static final String USER_ID = "id";
	private static final String USER_USER_NAME = "userName";
	private static final String USER_FULL_NAME = "fullName";
	private static final String USER_SEX = "sex";
	private static final String USER_BIRTHDAY = "birthday";
	private static final String USER_EMAIL = "email";
	private static final String USER_PASSWORD = "password";

	// Contacts Table Columns names
	private static final String NEWS_ID = "id";
	private static final String NEWS_PHOTO = "photo";
	private static final String NEWS_TITLE = "title";
	private static final String NEWS_CONTENT = "content";
	
	private static final String REPORT_ID = "id";
	private static final String REPORT_PHOTO = "photo";
	private static final String REPORT_TITLE = "title";
	private static final String REPORT_CONTENT = "content";
	
	private static final String SCORE_ID = "id";
	private static final String SCORE_NAME = "name";
	private static final String SCORE_SCORE = "score";
	private static final String SCORE_USER = "user";
	
	private static final String SCHEDULE_ID = "id";
	private static final String SCHEDULE_NAME = "name";
	private static final String SCHEDULE_TIME = "time";
	private static final String SCHEDULE_PLACE = "place";
	private static final String	SCHEDULE_USER = "user";


	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "(" + USER_ID
				+ " TEXT PRIMARY KEY," + USER_USER_NAME + " TEXT," + USER_FULL_NAME
				+ " TEXT," + USER_SEX + " TEXT," + USER_BIRTHDAY + " TEXT," + USER_EMAIL + " TEXT," + USER_PASSWORD + " TEXT" +" )";
		String CREATE_NEWS_TABLE = "CREATE TABLE " + TABLE_NEWS + "(" + NEWS_ID
				+ " INTEGER PRIMARY KEY," + NEWS_PHOTO + " TEXT," + NEWS_TITLE
				+ " TEXT," + NEWS_CONTENT + " TEXT" + " )";
		String CREATE_REPORT_TABLE = "CREATE TABLE " + TABLE_REPORT + "(" + REPORT_ID
				+ " INTEGER PRIMARY KEY," + REPORT_PHOTO + " TEXT," + REPORT_TITLE
				+ " TEXT," + REPORT_CONTENT + " TEXT" + " )";
		String CREATE_SCORE_TABLE = "CREATE TABLE " + TABLE_SCORE + "(" + SCORE_ID
				+ " STRING PRIMARY KEY," + SCORE_NAME + " TEXT," + SCORE_SCORE
				+ " TEXT," + SCORE_USER + " TEXT" + " )";
		String CREATE_SCHEDULE_TABLE = "CREATE TABLE " + TABLE_SCHEDULE + "(" + SCHEDULE_ID
				+ " STRING PRIMARY KEY," + SCHEDULE_NAME + " TEXT," + SCHEDULE_TIME
				+ " TEXT," + SCHEDULE_PLACE + " TEXT," + SCHEDULE_USER + " TEXT" + " )";
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_NEWS_TABLE);
		db.execSQL(CREATE_REPORT_TABLE);
		db.execSQL(CREATE_SCORE_TABLE);
		db.execSQL(CREATE_SCHEDULE_TABLE);
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		// Drop older table if existed
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_NEWS);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_REPORT);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCORE);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_SCHEDULE);

		// Create tables again
		onCreate(db);
	}
	
	public void addUser(User user) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(USER_ID, user.id); // Contact Name
		values.put(USER_USER_NAME, user.userName); // Contact Phone Number
		values.put(USER_FULL_NAME, user.fullName);
		values.put(USER_SEX, user.sex);
		values.put(USER_BIRTHDAY, user.birthday);
		values.put(USER_EMAIL, user.email);
		values.put(USER_PASSWORD, user.password);

		// Inserting Row
		db.insert(TABLE_USER, null, values);
		db.close(); // Closing database connection
	}

	public User getUser(String id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_USER, new String[] { USER_ID, USER_USER_NAME,
				USER_FULL_NAME, USER_SEX, USER_BIRTHDAY, USER_EMAIL, USER_PASSWORD }, USER_ID + "=?",
				new String[] { id }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		User user = new User(cursor.getString(0),
				cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
		// return contact
		return user;
	}
	
	public User getUserByUserName(String userName) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_USER, new String[] { USER_ID, USER_USER_NAME,
				USER_FULL_NAME, USER_SEX, USER_BIRTHDAY, USER_EMAIL, USER_PASSWORD }, USER_USER_NAME + "=?",
				new String[] { userName }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		User user = new User(cursor.getString(0),
				cursor.getString(1), cursor.getString(2), cursor.getString(3), cursor.getString(4), cursor.getString(5), cursor.getString(6));
		// return contact
		return user;
	}

	public List<User> getAllUser() {
		List<User> userList = new ArrayList<User>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_USER;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				User user = new User();
				user.id = cursor.getString(0);
				user.userName = cursor.getString(1);
				user.fullName = cursor.getString(2);
				user.sex = cursor.getString(3);
				user.birthday = cursor.getString(4);
				user.email = cursor.getString(5);
				user.password = cursor.getString(6);
				
				// Adding contact to list
				userList.add(user);
			} while (cursor.moveToNext());
		}

		// return contact list
		return userList;
	}
	
	public int updateUser(User user) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    values.put(USER_ID, user.id);
	    values.put(USER_USER_NAME, user.userName);
	    values.put(USER_FULL_NAME, user.fullName);
	    values.put(USER_SEX, user.sex);
	    values.put(USER_BIRTHDAY, user.birthday);
	    values.put(USER_EMAIL, user.email);
	    values.put(USER_PASSWORD, user.password);
	 
	    // updating row
	    return db.update(TABLE_USER, values, USER_ID + " = ?",
	            new String[] { String.valueOf(user.id) });
	}
	
	public void deleteUser(User user) {
	    SQLiteDatabase db = this.getWritableDatabase();
	    db.delete(TABLE_USER, USER_ID + " = ?",
	            new String[] { String.valueOf(user.id) });
	    db.close();
	}

	public void addTinTuc(TinTuc tinTuc) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(NEWS_PHOTO, tinTuc.getPhoto()); // Contact Name
		values.put(NEWS_TITLE, tinTuc.getTitle()); // Contact Phone Number
		values.put(NEWS_CONTENT, tinTuc.getContent());

		// Inserting Row
		db.insert(TABLE_NEWS, null, values);
		db.close(); // Closing database connection
	}

	public TinTuc getTinTuc(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_NEWS, new String[] { NEWS_ID, NEWS_PHOTO,
				NEWS_TITLE, NEWS_CONTENT }, NEWS_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		TinTuc tinTuc = new TinTuc(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2), cursor.getString(3));
		// return contact
		return tinTuc;
	}

	public List<TinTuc> getAllTinTuc() {
		List<TinTuc> tinTucList = new ArrayList<TinTuc>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_NEWS;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				TinTuc tinTuc = new TinTuc();
				tinTuc.setId(Integer.parseInt(cursor.getString(0)));
				tinTuc.setPhoto(cursor.getString(1));
				tinTuc.setTitle(cursor.getString(2));
				// Adding contact to list
				tinTucList.add(tinTuc);
			} while (cursor.moveToNext());
		}

		// return contact list
		return tinTucList;
	}
	
	public int updateTinTuc(TinTuc tinTuc) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    values.put(NEWS_PHOTO, tinTuc.getPhoto());
	    values.put(NEWS_TITLE, tinTuc.getTitle());
	    values.put(NEWS_CONTENT, tinTuc.getContent());
	 
	    // updating row
	    return db.update(TABLE_NEWS, values, NEWS_ID + " = ?",
	            new String[] { String.valueOf(tinTuc.getId()) });
	}
	
	public void deleteTinTuc(TinTuc tinTuc) {
	    SQLiteDatabase db = this.getWritableDatabase();
	    db.delete(TABLE_NEWS, NEWS_ID + " = ?",
	            new String[] { String.valueOf(tinTuc.getId()) });
	    db.close();
	}
	
	public void addThongBao(ThongBao thongBao) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(REPORT_PHOTO, thongBao.getPhoto()); // Contact Name
		values.put(REPORT_TITLE, thongBao.getTitle()); // Contact Phone Number
		values.put(REPORT_CONTENT, thongBao.getContent());

		// Inserting Row
		db.insert(TABLE_REPORT, null, values);
		db.close(); // Closing database connection
	}

	public ThongBao getThongBao(int id) {
		SQLiteDatabase db = this.getReadableDatabase();

		Cursor cursor = db.query(TABLE_REPORT, new String[] { REPORT_ID, REPORT_PHOTO,
				REPORT_TITLE, REPORT_CONTENT }, REPORT_ID + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		ThongBao thongBao = new ThongBao(Integer.parseInt(cursor.getString(0)),
				cursor.getString(1), cursor.getString(2), cursor.getString(3));
		// return contact
		return thongBao;
	}

	public List<ThongBao> getAllThongBao() {
		List<ThongBao> thongBaoList = new ArrayList<ThongBao>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_REPORT;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				ThongBao thongBao = new ThongBao();
				thongBao.setId(Integer.parseInt(cursor.getString(0)));
				thongBao.setPhoto(cursor.getString(1));
				thongBao.setTitle(cursor.getString(2));
				// Adding contact to list
				thongBaoList.add(thongBao);
			} while (cursor.moveToNext());
		}

		// return contact list
		return thongBaoList;
	}
	
	public int updateThongBao(ThongBao thongBao) {
	    SQLiteDatabase db = this.getWritableDatabase();
	 
	    ContentValues values = new ContentValues();
	    values.put(REPORT_PHOTO, thongBao.getPhoto());
	    values.put(REPORT_TITLE, thongBao.getTitle());
	    values.put(REPORT_CONTENT, thongBao.getContent());
	 
	    // updating row
	    return db.update(TABLE_REPORT, values, REPORT_ID + " = ?",
	            new String[] { String.valueOf(thongBao.getId()) });
	}
	
	public void deleteThongBao(ThongBao thongBao) {
	    SQLiteDatabase db = this.getWritableDatabase();
	    db.delete(TABLE_REPORT, REPORT_ID + " = ?",
	            new String[] { String.valueOf(thongBao.getId()) });
	    db.close();
	}
	
	public List<Diem> getAllDiem(String id) {
		List<Diem> scoreList = new ArrayList<Diem>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_SCORE + " WHERE " + SCORE_USER + "=\"" + id + "\"";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Diem diem = new Diem();
				diem.setId(cursor.getString(0));
				diem.setName(cursor.getString(1));
				diem.setScore(Double.parseDouble(cursor.getString(2)));
				diem.setUser(cursor.getString(3));
				
				// Adding contact to list
				scoreList.add(diem);
			} while (cursor.moveToNext());
		}

		// return contact list
		return scoreList;
	}
	
	public List<Tkb> getAllTkb(String id) {
		List<Tkb> tkbList = new ArrayList<Tkb>();
		// Select All Query
		String selectQuery = "SELECT  * FROM " + TABLE_SCHEDULE + " WHERE " + SCHEDULE_USER + "=\"" + id + "\"";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				Tkb tkb = new Tkb();
				tkb.setId(cursor.getString(0));
				tkb.setName(cursor.getString(1));
				tkb.setTime(cursor.getString(2));
				tkb.setPlace(cursor.getString(3));
				tkb.setUser(cursor.getString(4));
				
				// Adding contact to list
				tkbList.add(tkb);
			} while (cursor.moveToNext());
		}

		// return contact list
		return tkbList;
	}
}